use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Candidates')
  drop table Candidates
go

create table Candidates
(
  Id int identity(1,1) not null,
  EnterDate datetime not null,
  DocumnetNumber nvarchar(255) null,
  DocumentSum numeric(19,2) null,
  LastName nvarchar(255) not null,
  DateOfBirthDay datetime null,
  Adress nvarchar(255) null,
  ApproveOnPost bit not null, 
  PrePaid nvarchar(255) null,
  ManagerId int not null,
  [Resume] image null,
  [ResumeName] nvarchar(255) null,
  SexID int not null,
  CarID int null,
  Education nvarchar(255) null,
  EducationType nvarchar(255) null,
  Characteristic text null,
  TerritoryID int null,
  SalaryRangeID int null,
  PositionID int null,
  Position_CompareImportantID int null,
  Territory_CompareImportantID int null,
  SalaryRange_CompareImportantID int null,
  OtherReqirments text null,
  OnlyTradeMarks nvarchar(255),
  RangeTerritoryID int null
)  
go

alter table Candidates add constraint PK_Candidates_ID primary key(Id)
go


alter table Candidates with check add constraint DF_Candidates_ApproveOnPost
      default (0) for ApproveOnPost
go


alter table Candidates add constraint FK_Candidates_ManagerId 
      foreign key (ManagerId) references Users(Id) on delete cascade
go


alter table Candidates add constraint FK_Candidates_Positions 
      foreign key (PositionID) references Positions(Id) 
go

create nonclustered index IX_Candidates_ManagerId on Candidates
       (ManagerId asc)
go


alter table Candidates add constraint FK_Candidates_SalaryRanges_SalaryRangeID 
      foreign key (SalaryRangeID) references SalaryRanges(Id) 
go


alter table Candidates add constraint FK_Candidates_Territories_TerritoryId 
      foreign key (TerritoryId) references Territories(Id) 
go


alter table Candidates add constraint FK_Candidates_Territories_RangeTerritoryId 
      foreign key (RangeTerritoryId) references Territories(Id) 
go

alter table Candidates add constraint FK_Candidates_HasCar_CarID 
      foreign key (CarID) references HasCar(Id) 
go


alter table Candidates add constraint FK_Candidates_SexVariants_SexID 
      foreign key (SexID) references SexVariants(Id) 
go


alter table Candidates add constraint FK_Candidates_CompareImportants_Position_CompareImportantID 
      foreign key (Position_CompareImportantID) references CompareImportants(Id) 
go


alter table Candidates add constraint FK_Candidates_CompareImportants_Territory_CompareImportantID 
      foreign key (Territory_CompareImportantID) references CompareImportants(Id) 
go


alter table Candidates add constraint FK_Candidates_CompareImportants_SalaryRange_CompareImportantID 
      foreign key (SalaryRange_CompareImportantID) references CompareImportants(Id) 
go


--alter table Candidates with check add constraint CK_Candidates_Position_Choose
--check ((PositionID is null and Position_CompareImportantID is null) or
--       (PositionID is not null and Position_CompareImportantID is not null))
--go


--alter table Candidates with check add constraint CK_Candidates_SalaryRange_Choose
--check ((SalaryRangeID is null and SalaryRange_CompareImportantID is null) or
--       (SalaryRangeID is not null and SalaryRange_CompareImportantID is not null))
--go

--alter table Candidates with check add constraint CK_Candidates_Territory_Choose
--check ((TerritoryID is null and Territory_CompareImportantID is null) or
--       (TerritoryID is not null and Territory_CompareImportantID is not null))
--go

